package test;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class RemoveFutureData {

	/**
	 * @param args
	 * @throws SQLException 
	 */
	public static void main(String[] args) throws SQLException {
		Connection conn = DriverManager.getConnection("jdbc:mysql://www.smart-tracking.com:9999/gpsMap?characterEncoding=utf-8", "cong", "29749716");
		Statement stmt = conn.createStatement();
		String time = "2018-02-28"; // 注意修改时间
		String sql = "select r.tracetime,r.trackerid,r.realtimedataid,t.usertrackerid from realtimedata r inner join tracker t on r.trackerid=t.trackerid where r.tracetime>'"+time+"'";
		ResultSet rs = stmt.executeQuery(sql);
		while(rs.next()){
			String imei = rs.getString("usertrackerid");
			String trackerid = rs.getString("trackerid");
			Statement smt2 = conn.createStatement();
			// 注意此次时间
			ResultSet rs2 = smt2.executeQuery("select reportid,createtime from r_"+imei+" where createtime>'"+time+"'");
			Statement smt3 = conn.createStatement();
			while(rs2.next()){
				System.out.println("delete "+rs2.getInt("reportid")+",trackerid="+trackerid+",imei="+imei);
				smt3.executeUpdate("delete from r_"+imei+" where reportId="+rs2.getInt("reportid"));
			}
			smt3.close();
			smt2.close();
			smt2 = conn.createStatement();
			smt2.executeUpdate("delete from realtimedata where  trackerid='"+trackerid+"' and tracetime>'2012-02-26';");
			smt2.close();
		}
        rs.close();
	}

}
